#grant all privileges on *.* to beaver@localhost identified by 'angry' with grant option;

drop database studentsdb;
create database studentsdb;
use studentsdb;

create table students(sid int NOT NULL AUTO_INCREMENT PRIMARY KEY , name varchar(25), reg_no varchar(10) UNIQUE, year_of_study int);
create table courses(cid int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(25), year int, semester int, professor varchar(25));
create table relations(rid int NOT NULL AUTO_INCREMENT PRIMARY KEY, cid int NOT NULL, sid int NOT NULL, FOREIGN KEY (cid) references courses(cid), FOREIGN KEY (sid) references students(sid));

#-----------------------------------------------------------
#-----------------------------------------------------------
#-----------------------------------------------------------
#--- Students' Section
#-----------------------------------------------------------
#sid, student_name, regno, year_of_study
#insert into students values (null, 'John Wayne', 'LM5000', 1);
#insert into students values (null, 'Robin Hood', 'LM2000', 1);

#students for year1
insert into students values (null, 'Student1_year1', 'LM1001', 1);
insert into students values (null, 'Student2_year1', 'LM1002', 1);
insert into students values (null, 'Student3_year1', 'LM1003', 1);
insert into students values (null, 'Student4_year1', 'LM1004', 1);
insert into students values (null, 'Student5_year1', 'LM1005', 1);

#students for year2
insert into students values (null, 'Student1_year2', 'LM2001', 2);
insert into students values (null, 'Student2_year2', 'LM2002', 2);
insert into students values (null, 'Student3_year2', 'LM2003', 2);
insert into students values (null, 'Student4_year2', 'LM2004', 2);
insert into students values (null, 'Student5_year2', 'LM2005', 2);

#students for year3
insert into students values (null, 'Student1_year3', 'LM3001', 3);
insert into students values (null, 'Student2_year3', 'LM3002', 3);
insert into students values (null, 'Student3_year3', 'LM3003', 3);
insert into students values (null, 'Student4_year3', 'LM3004', 3);
insert into students values (null, 'Student5_year3', 'LM3005', 3);

#students for year4
insert into students values (null, 'Student1_year4', 'LM4001', 4);
insert into students values (null, 'Student2_year4', 'LM4002', 4);
insert into students values (null, 'Student3_year4', 'LM4003', 4);
insert into students values (null, 'Student4_year4', 'LM4004', 4);
insert into students values (null, 'Student5_year4', 'LM4005', 4);



#-----------------------------------------------------------
#-----------------------------------------------------------
#-----------------------------------------------------------
#--- Course Section
#-----------------------------------------------------------
#!!! cid, course_name, year, semester, prof_name) !!!
#insert into courses values (null, 'Math', 1, 1, 'George the Math Teacher');
#insert into courses values (null, 'Philosophy', 1, 2, 'Jim the Philosopher');
#insert into courses values (null, 'Retorics', 2, 1, 'Joe the Speaker');

#year 1, semester 1
insert into courses values (null, 'Course1_Year1_Sem1', 1, 1, 'Professor_111');
insert into courses values (null, 'Course2_Year1_Sem1', 1, 1, 'Professor_211');
insert into courses values (null, 'Course3_Year1_Sem1', 1, 1, 'Professor_311');
insert into courses values (null, 'Course4_Year1_Sem1', 1, 1, 'Professor_411');
insert into courses values (null, 'Course5_Year1_Sem1', 1, 1, 'Professor_511');
insert into courses values (null, 'Course6_Year1_Sem1', 1, 1, 'Professor_611');

#year 1, semester 2
insert into courses values (null, 'Course1_Year1_Sem2', 1, 2, 'Professor_112');
insert into courses values (null, 'Course2_Year1_Sem2', 1, 2, 'Professor_212');
insert into courses values (null, 'Course3_Year1_Sem2', 1, 2, 'Professor_312');
insert into courses values (null, 'Course4_Year1_Sem2', 1, 2, 'Professor_412');
insert into courses values (null, 'Course5_Year1_Sem2', 1, 2, 'Professor_512');


#year 2, semester 1
insert into courses values (null, 'Course1_Year2_Sem1', 2, 1, 'Professor_121');
insert into courses values (null, 'Course2_Year2_Sem1', 2, 1, 'Professor_221');
insert into courses values (null, 'Course3_Year2_Sem1', 2, 1, 'Professor_321');
insert into courses values (null, 'Course4_Year2_Sem1', 2, 1, 'Professor_421');
insert into courses values (null, 'Course5_Year2_Sem1', 2, 1, 'Professor_521');
insert into courses values (null, 'Course6_Year2_Sem1', 2, 1, 'Professor_621');
insert into courses values (null, 'Course7_Year2_Sem1', 2, 1, 'Professor_721');

#year 2, semester 2
insert into courses values (null, 'Course1_Year2_Sem2', 2, 2, 'Professor_122');
insert into courses values (null, 'Course2_Year2_Sem2', 2, 2, 'Professor_222');
insert into courses values (null, 'Course3_Year2_Sem2', 2, 2, 'Professor_322');
insert into courses values (null, 'Course4_Year2_Sem2', 2, 2, 'Professor_422');
insert into courses values (null, 'Course5_Year2_Sem2', 2, 2, 'Professor_522');


#year 3, semester 1
insert into courses values (null, 'Course1_Year3_Sem1', 3, 1, 'Professor_131');
insert into courses values (null, 'Course2_Year3_Sem1', 3, 1, 'Professor_231');
insert into courses values (null, 'Course3_Year3_Sem1', 3, 1, 'Professor_331');
insert into courses values (null, 'Course4_Year3_Sem1', 3, 1, 'Professor_431');
insert into courses values (null, 'Course5_Year3_Sem1', 3, 1, 'Professor_531');

#year 3, semester 2
insert into courses values (null, 'Course1_Year3_Sem2', 3, 2, 'Professor_132');
insert into courses values (null, 'Course2_Year3_Sem2', 3, 2, 'Professor_232');
insert into courses values (null, 'Course3_Year3_Sem2', 3, 2, 'Professor_332');
insert into courses values (null, 'Course4_Year3_Sem2', 3, 2, 'Professor_432');
insert into courses values (null, 'Course5_Year3_Sem2', 3, 2, 'Professor_532');

#year 4, semester 1
insert into courses values (null, 'Course1_Year4_Sem1', 4, 1, 'Professor_141');
insert into courses values (null, 'Course2_Year4_Sem1', 4, 1, 'Professor_241');
insert into courses values (null, 'Course3_Year4_Sem1', 4, 1, 'Professor_341');
insert into courses values (null, 'Course4_Year4_Sem1', 4, 1, 'Professor_441');
insert into courses values (null, 'Course5_Year4_Sem1', 4, 1, 'Professor_541');
insert into courses values (null, 'Course6_Year4_Sem1', 4, 1, 'Professor_641');

#year 4, semester 2
insert into courses values (null, 'Course1_Year4_Sem2', 4, 2, 'Professor_142');
insert into courses values (null, 'Course2_Year4_Sem2', 4, 2, 'Professor_242');
insert into courses values (null, 'Course3_Year4_Sem2', 4, 2, 'Professor_342');
insert into courses values (null, 'Course4_Year4_Sem2', 4, 2, 'Professor_442');
insert into courses values (null, 'Course5_Year4_Sem2', 4, 2, 'Professor_542');
insert into courses values (null, 'Course6_Year4_Sem2', 4, 2, 'Professor_642');
insert into courses values (null, 'Course7_Year4_Sem2', 4, 2, 'Professor_742');

